﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using NewDDSys.IDAL;
using NewDDSys.Entity;
using NewDDSys.Utility;
using System.Text;

namespace NewDDSys.ORADAL
{
    public class RkcdtDAL:IRkcdtDAL
    {
        public int Add(RkcdtInfo dt)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dt.Dtnum),
                DALHelper.MakeInParam(":num", OracleType.VarChar, dt.Num),
                DALHelper.MakeInParam(":banzu", OracleType.VarChar, dt.Banzu),
                DALHelper.MakeInParam(":tianbao", OracleType.VarChar, dt.Tianbao),
                DALHelper.MakeInParam(":kday", OracleType.VarChar, dt.Kday)
            };
            return (DALHelper.RunCommand(@"insert into dd.RKCDT (dtnum, num, banzu, tianbao, kday) values (:dtnum, :num, :banzu, :tianbao, to_date(:kday,'yyyy-mm-dd hh24:mi:ss'))", prams));
        }
        public int Update(string dtnum, decimal kn, decimal knwj, decimal knkl, decimal dk, decimal dkwj, decimal dkkl)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":kn", OracleType.Number, kn),
                DALHelper.MakeInParam(":knwj", OracleType.Number, knwj),
                DALHelper.MakeInParam(":knkl", OracleType.Number, knkl),
                DALHelper.MakeInParam(":dk", OracleType.Number, dk),
                DALHelper.MakeInParam(":dkwj", OracleType.Number, dkwj),
                DALHelper.MakeInParam(":dkkl", OracleType.Number, dkkl)
            };
            return (DALHelper.RunCommand(@"update dd.RKCDT set kn = :kn, knwj = :knwj, knkl = :knkl, dk = :dk, dkwj = :dkwj, dkkl = :dkkl where dtnum = :dtnum", prams));
        }
        public int UpdateKN(string dtnum, decimal kn)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":kn", OracleType.Number, kn)
            };
            return (DALHelper.RunCommand(@"update dd.RKCDT set kn = :kn where dtnum = :dtnum", prams));
        }
        public int UpdateKNWJ(string dtnum, decimal knwj)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":knwj", OracleType.Number, knwj)
            };
            return (DALHelper.RunCommand(@"update dd.RKCDT set knwj = :knwj where dtnum = :dtnum", prams));
        }
        public int UpdateKNKL(string dtnum, decimal knkl)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":knkl", OracleType.Number, knkl)
            };
            return (DALHelper.RunCommand(@"update dd.RKCDT set knkl = :knkl where dtnum = :dtnum", prams));
        }
        public int UpdateDK(string dtnum, decimal dk)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":dk", OracleType.Number, dk)
            };
            return (DALHelper.RunCommand(@"update dd.RKCDT set dk = :dk where dtnum = :dtnum", prams));
        }
        public int UpdateDKWJ(string dtnum, decimal dkwj)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":dkwj", OracleType.Number, dkwj)
            };
            return (DALHelper.RunCommand(@"update dd.RKCDT set dkwj = :dkwj where dtnum = :dtnum", prams));
        }
        public int UpdateDKKL(string dtnum, decimal dkkl)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum),
                DALHelper.MakeInParam(":dkkl", OracleType.Number, dkkl)
            };
            return (DALHelper.RunCommand(@"update dd.RKCDT set dkkl = :dkkl where dtnum = :dtnum", prams));
        }
        public int Del(string dtnum)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum)
            };
            return (DALHelper.RunCommand(@"delete from dd.RKCDT where DTNUM = :dtnum", prams));
        }
        public DataSet Select(string dtnum)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":dtnum", OracleType.VarChar, dtnum)
            };
            return (DALHelper.RunProcReturn(@"select * from dd.RKCDT where dtnum = :dtnum", prams, "jl"));
        }
        public DataSet SelectAll(string ku)
        {
            string nowtime = DateTime.Now.ToShortTimeString().ToString();
            string time1 = "08:00:00";
            string time2 = "20:00:00";
            DateTime dt1 = Convert.ToDateTime(nowtime);
            DateTime dt2 = Convert.ToDateTime(time1);
            DateTime dt3 = Convert.ToDateTime(time2);
            string bt = "";
            string et = "";
            if (DateTime.Compare(dt1, dt2) >= 0 && DateTime.Compare(dt1, dt3) <= 0)
            {
                bt = dt2.ToString();
                et = dt3.ToString();
            }
            else if (DateTime.Compare(dt1, dt2) < 0)
            {
                bt = dt3.AddDays(-1).ToString();
                et = dt1.ToString();
            }
            else
            {
                bt = dt3.ToString();
                et = dt1.ToString();
            }
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            return (DALHelper.RunProcReturn(@"select dt.dtnum as dtnum, jg.ku as ku, jg.pinzhong as pinzhong, jg.guige as guige, dt.kday as kday, dt.banzu as banzu, dt.tianbao as tianbao, dt.kn as kn, dt.knwj as knwj, dt.knkl as knkl, dt.dk as dk, dt.dkwj as dkwj, dt.dkkl as dkkl from dd.RKCDT dt, dd.KC jg where dt.num = jg.num and jg.ku = :ku and dt.kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and dt.kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams, "jlall"));

        }
        public DataSet SelectPG(string ku)
        {
            string nowtime = DateTime.Now.ToShortTimeString().ToString();
            string time1 = "08:00:00";
            string time2 = "20:00:00";
            DateTime dt1 = Convert.ToDateTime(nowtime);
            DateTime dt2 = Convert.ToDateTime(time1);
            DateTime dt3 = Convert.ToDateTime(time2);
            string bt = "";
            string et = "";
            if (DateTime.Compare(dt1, dt2) >= 0 && DateTime.Compare(dt1, dt3) <= 0)
            {
                bt = dt2.ToString();
                et = dt3.ToString();
            }
            else if (DateTime.Compare(dt1, dt2) < 0)
            {
                bt = dt3.AddDays(-1).ToString();
                et = dt1.ToString();
            }
            else
            {
                bt = dt3.ToString();
                et = dt1.ToString();
            }
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            return (DALHelper.RunProcReturn(@"select num, pinzhong, guige from dd.KC where ku = :ku and num not in (select num from dd.RKCDT where kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss'))", prams, "pg"));

        }
        public string KuKC(string cday, string ku)
        {
            
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":ku", OracleType.VarChar, ku),
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dt.kn) from dd.RKCDT dt, dd.KC jg where dt.num = jg.num and jg.ku = :ku and dt.kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and dt.kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            return obj.ToString();
        }
        public string JDJB(string cday)
        {
            
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dk) from dd.RKCDT where kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            return obj.ToString();
        }
        public string WJK(string cday)
        {
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dkwj + knwj) from dd.RKCDT where kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            return obj.ToString();
        }
        public string KLY(string cday)
        {
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dkkl + knkl) from dd.RKCDT where kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            return obj.ToString();
        }
        public string ZKC(string cday)
        {
            string et = Convert.ToDateTime(cday).AddHours(8).ToString();
            string bt = Convert.ToDateTime(et).AddHours(-12).ToString();
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":bt", OracleType.VarChar, bt),
                DALHelper.MakeInParam(":et", OracleType.VarChar, et)
            };
            object obj = DALHelper.ExecuteSQLScalar(@"select sum(dk + kn) from dd.RKCDT where kday >= to_date(:bt,'yyyy-mm-dd hh24:mi:ss') and kday <= to_date(:et,'yyyy-mm-dd hh24:mi:ss')", prams);
            if (obj == null)
                return "";
            else
                return obj.ToString();
        }

    }
}
